package ch.sv7.tool.dbmanager.db;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import ch.sv7.tool.dbmanager.db.difference.ExtraColumnDifference;
import ch.sv7.tool.dbmanager.db.difference.ExtraConstraintDifference;
import ch.sv7.tool.dbmanager.db.difference.ExtraForeignKeyDifference;
import ch.sv7.tool.dbmanager.db.difference.ExtraIndexDifference;
import ch.sv7.tool.dbmanager.db.difference.MissingColumnDifference;
import ch.sv7.tool.dbmanager.db.difference.MissingConstraintDifference;
import ch.sv7.tool.dbmanager.db.difference.MissingForeignKeyDifference;
import ch.sv7.tool.dbmanager.db.difference.MissingIndexDifference;
import ch.sv7.tool.dbmanager.db.difference.SchemaDifferences;
import ch.sv7.tool.dbmanager.db.utils.CompareTool;

public class TableSchema implements SchemaElement, Serializable {

	private static final long serialVersionUID = 1L;

	private static final boolean PUT_PK_FIRST = false;
	private static final String NB_ROWS_KEY = "NB_ROWS_KEY";
	
	protected String name;

	protected String schema;

	protected List columns;
	
	protected List constraints = new ArrayList();
	
	protected List indexes = new ArrayList();
	protected List foreignKeys = new ArrayList();
	protected List referencedForeignKeys = new ArrayList();
	protected Properties properties = new Properties();

	public TableSchema(String name, String schema) {
		this.name = name;
		this.schema = schema;
		columns = new ArrayList();
	}
	
	public boolean isTableEmpty() {
		return getNbRows() == 0;
	}
	
	public void setNbRows(long nbRows) {
		properties.setProperty(NB_ROWS_KEY, Long.toString(nbRows));
	}
	
	public long getNbRows() {
		String value = properties.getProperty(NB_ROWS_KEY, "-1");
		try {
			return Long.parseLong(value);
		} catch (NumberFormatException e) {
			return -1;
		}
	}
	public void buildIndexReferences() {
		Iterator cols = columns.iterator();
		while (cols.hasNext()){
			ColumnSchema cs = (ColumnSchema)cols.next();
			cs.getReferencedIndexes().clear();
		}
		
		Iterator indexIter = indexes.iterator();
		while (indexIter.hasNext()) {
			IndexSchema index = (IndexSchema)indexIter.next();
			Iterator colsIter = index.getColumnSet().iterator();
			while (colsIter.hasNext()) {
				String column = (String)colsIter.next();
				ColumnSchema cs = getColumnSchema(column);
				if (cs != null) {
					cs.getReferencedIndexes().add(index);
				} else {
					System.out.println("Unable to find column " + column + " referenced by index " + index.getName());
				}
			}
		}
	}
	
	/**
	 * @return the referencedForeignKeys
	 */
	public List getReferencedForeignKeys() {
		return referencedForeignKeys;
	}

	public void buildForeignKeysReferences(Map tables) {
		Iterator cols = columns.iterator();
		while (cols.hasNext()){
			ColumnSchema cs = (ColumnSchema)cols.next();
			cs.getReferencedForeignKeys().clear();
		}
		
		Iterator fkIter = foreignKeys.iterator();
		while (fkIter.hasNext()) {
			ForeignKeySchema fk = (ForeignKeySchema)fkIter.next();
			referencedForeignKeys.add(fk);
			Iterator colsIter = fk.getColumnsSet().iterator();
			while (colsIter.hasNext()) {
				String column = (String)colsIter.next();
				ColumnSchema cs = getColumnSchema(column);
				if (cs != null) {
					cs.getReferencedForeignKeys().add(fk);
				} else {
					System.out.println("Unable to find column " + column + " referenced by fk " + fk.getName());
				}
			}
			
			TableSchema remoteTable = (TableSchema)tables.get(fk.getForeignTable());
			if (remoteTable != null) {
				remoteTable.getReferencedForeignKeys().add(fk);
				Iterator fkColsIter = fk.getForeignColumnsSet().iterator();
				while (fkColsIter.hasNext()) {
					String column = (String)fkColsIter.next();
					ColumnSchema cs = remoteTable.getColumnSchema(column);
					if (cs != null) {
						cs.getReferencedForeignKeys().add(fk);
					} else {
						System.out.println("Unable to find column " + column + " referenced by fk " + fk.getName());
					}
					
				}
			} else {
				System.out.println("Unable to find table " + fk.getForeignTable() + " referenced by fk " + fk.getName());
			}
		}
	}
	/**
	 * @return the indexes
	 */
	public List getIndexes() {
		return indexes;
	}

	/**
	 * @return the indexes
	 */
	public List getPrimaryKeyIndexes() {
		List pks = new ArrayList();
		Iterator iter = indexes.iterator();
		while (iter.hasNext()){
			IndexSchema index = (IndexSchema)iter.next();
			if (index.isPrimaryKey()){
				pks.add(index);
			}
		}
		return pks;
	}

	/**
	 * @param indexes the indexes to set
	 */
	public void setIndexes(List indexes) {
		this.indexes = indexes;
	}

	/**
	 * @return the constraints
	 */
	public List getConstraints() {
		return constraints;
	}

	/**
	 * @return the foreignKeys
	 */
	public List getForeignKeys() {
		return foreignKeys;
	}

	/**
	 * @param foreignKeys the foreignKeys to set
	 */
	public void setForeignKeys(List foreignKeys) {
		this.foreignKeys = foreignKeys;
	}

	public synchronized ColumnSchema[] getColumnSchemas() {
		return (ColumnSchema[]) columns.toArray(new ColumnSchema[0]);
	}

	public synchronized ColumnSchema[] getPrimaryKeyColumnSchemas() {
		ArrayList list = new ArrayList();
		for (int i = 0; i < columns.size(); i++) {
			ColumnSchema cs = (ColumnSchema) columns.get(i);
			if (cs.isPrimaryKey())
				list.add(cs);
		}

		return (ColumnSchema[]) list.toArray(new ColumnSchema[0]);
	}

	public synchronized ColumnSchema getColumnSchema(String name) {
		for (int i = 0; i < columns.size(); i++) {
			ColumnSchema cs = (ColumnSchema) columns.get(i);
			if (cs.getName().equals(name))
				return cs;
		}

		return null;
	}

	public synchronized void addColumnSchema(ColumnSchema cs) {
		if (PUT_PK_FIRST){
			int pkCount = 0;
			for (int i=0; i<columns.size(); i++) {
				ColumnSchema col = (ColumnSchema)columns.get(i);
				if (col.isPrimaryKey()){
					pkCount++;
				}
			}
			if (cs.isPrimaryKey()) {
				columns.add(pkCount, cs);
			} else {
				columns.add(cs);			
			}
		} else {
			columns.add(cs);
		}
	}
	
	public synchronized void removeColumnSchema(String columnName) {
		ColumnSchema cs = getColumnSchema(columnName);
		columns.remove(cs);
	}

	public String getName() {
		return name;
	}

	public String getFullName() {
		if (schema != null && schema.length() > 0)
			return schema + "." + name;
		else
			return name;
	}

	public int size() {
		return columns.size();
	}

	public IndexSchema getIndexByName(String name) {
		if (indexes != null) {
			Iterator iter = indexes.iterator();
			while (iter.hasNext()){
				IndexSchema index = (IndexSchema)iter.next();
				if (index.getName().equals(name)){
					return index;
				}
			}
		}
		return null;
	}

	public String getSchemaIdentifier() {
		return "Table: " + getName();
	}

	/**
	 * @return the properties
	 */
	public Properties getProperties() {
		return properties;
	}

	public void computeDifferencesFromActual(SchemaDifferences diffs, TableSchema actualTableSchema) {
		ColumnSchema refCols[] = getColumnSchemas();
		ColumnSchema actualCols[] = actualTableSchema.getColumnSchemas();
		Set differentColumns = new HashSet();
		for (int i = 0; i < refCols.length; i++) {
			ColumnSchema cs = actualTableSchema.getColumnSchema(refCols[i].getName());
			if (cs == null)
				diffs.addDifference(this, new MissingColumnDifference(this, actualTableSchema, refCols[i], null));
			else if (!refCols[i].equals(cs))
				differentColumns.add(refCols[i].getName());
		}

		for (int i = 0; i < actualCols.length; i++) {
			ColumnSchema cs = getColumnSchema(actualCols[i].getName());
			if (cs == null)
				diffs.addDifference(this, new ExtraColumnDifference(this, actualTableSchema, null, actualCols[i]));
			else if (!actualCols[i].equals(cs))
				differentColumns.add(actualCols[i].getName());
		}

		ColumnSchema refCs;
		ColumnSchema actualCs;
		for (Iterator iter = differentColumns.iterator(); iter.hasNext(); ) {
			String colName = (String) iter.next();
			refCs = getColumnSchema(colName);
			actualCs = actualTableSchema.getColumnSchema(colName);
			refCs.computeDifferencesFromActual(diffs, actualCs);
		}

		CompareTool.compareList(diffs, actualTableSchema, this, indexes, actualTableSchema.indexes, MissingIndexDifference.class, ExtraIndexDifference.class);
		CompareTool.compareList(diffs, actualTableSchema, this, foreignKeys, actualTableSchema.foreignKeys, MissingForeignKeyDifference.class, ExtraForeignKeyDifference.class);
		CompareTool.compareList(diffs, actualTableSchema, this, constraints, actualTableSchema.constraints, MissingConstraintDifference.class, ExtraConstraintDifference.class);

	}

	public String toString() {
		String result = "Table " + name + "\n";
		for (int i = 0; i < columns.size(); i++) {
			ColumnSchema col = (ColumnSchema) columns.get(i);
			result = result + col.toString();
		}

		return result;
	}

	public String getSchema() {
		return schema;
	}

	public void forceLowerCase() {
		name = name.toLowerCase();
		if (schema != null) {
			schema = schema.toLowerCase();
		}
		ColumnSchema[] cols = getColumnSchemas();
		for (int i = 0; i < cols.length; i++) {
			ColumnSchema cs = cols[i];
			cs.forceLowerCase();
		}
	}

	public void forceUpperCase() {
		name = name.toLowerCase();
		if (schema != null) {
			schema = schema.toLowerCase();
		}
		ColumnSchema[] cols = getColumnSchemas();
		for (int i = 0; i < cols.length; i++) {
			ColumnSchema cs = cols[i];
			cs.forceUpperCase();
		}
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#hashCode()
	 */
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + ((columns == null) ? 0 : columns.hashCode());
		result = prime * result + ((foreignKeys == null) ? 0 : foreignKeys.hashCode());
		result = prime * result + ((indexes == null) ? 0 : indexes.hashCode());
		result = prime * result + ((name == null) ? 0 : name.hashCode());
		result = prime * result + ((schema == null) ? 0 : schema.hashCode());
		return result;
	}

	/* (non-Javadoc)
	 * @see java.lang.Object#equals(java.lang.Object)
	 */
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		final TableSchema other = (TableSchema) obj;
		if (columns == null) {
			if (other.columns != null)
				return false;
		} else if (!columns.equals(other.columns))
			return false;
		if (foreignKeys == null) {
			if (other.foreignKeys != null)
				return false;
		} else if (!foreignKeys.equals(other.foreignKeys))
			return false;
		if (indexes == null) {
			if (other.indexes != null)
				return false;
		} else if (!indexes.equals(other.indexes))
			return false;
		if (name == null) {
			if (other.name != null)
				return false;
		} else if (!name.equals(other.name))
			return false;
		if (schema == null) {
			if (other.schema != null)
				return false;
		} else if (!schema.equals(other.schema))
			return false;
		return true;
	}

	
	
	
}
